insert overwrite table jms_dws.dws_wide_unsign_whole_effect_dt
select
      waybill_no
     ,taking_date              --实际揽收日期
     ,sign_date                --实效签收日期
     ,send_network_code        --始发网点编码          
     ,send_network_name        --始发网点名称            
     ,send_area_id             --始发区县id          
     ,send_area_desc           --始发区县名称          
     ,send_city_id             --始发城市id          
     ,send_city_desc           --始发城市名称          
     ,send_agent_code          --始发代理区code       
     ,send_agent_name          --始发代理区名称         
     ,send_fran_code           --始发加盟商code       
     ,send_fran_name           --始发加盟商名称         
     ,send_provider_id         --始发省份id          
     ,send_provider_desc       --始发省份名称          
     ,send_regional_id         --始发大区id          
     ,send_regional_desc       --始发大区名称       
     ,sign_network_code        --签收网点编码          
     ,sign_network_name        --签收网点名称           
     ,sign_area_id             --签收区县id          
     ,sign_area_desc           --签收区县名称          
     ,sign_city_id             --签收城市id          
     ,sign_city_desc           --签收城市名称          
     ,sign_agent_code          --签收代理区code       
     ,sign_agent_name          --签收代理区名称         
     ,sign_fran_code           --签收加盟商code       
     ,sign_fran_name           --签收加盟商名称         
     ,sign_provider_id         --签收省份id          
     ,sign_provider_desc       --签收省份名称          
     ,sign_regional_id         --签收大区id          
     ,sign_regional_desc       --签收大区名称  
     ,ordersource_code         --多平台订单来源编码
     ,ordersource_name         --多平台订单来源名称
     ,deliver_user_code        --派件员编码
     ,deliver_user_name        --派件员名称
     ,if(pre_sign_taking_difftime            >0,pre_sign_taking_difftime            ,0) as pre_sign_taking_difftime            --时效签收时间和揽收时间差
     ,if(real_sign_taking_difftime           >0,real_sign_taking_difftime           ,0) as real_sign_taking_difftime           --实际签收时间和揽收时间差
     ,if(first_center_taking_difftime        >0,first_center_taking_difftime        ,0) as first_center_taking_difftime        --揽收和入首中心时间差
     ,if(inout_first_center_difftime         >0,inout_first_center_difftime         ,0) as inout_first_center_difftime         --出首中心时间和入首中心时间差
     ,if(dest_first_center_difftime          >0,dest_first_center_difftime          ,0) as dest_first_center_difftime          --目的网点到件时间和入首中心时间差
     ,if(last_first_center_difftime          >0,last_first_center_difftime          ,0) as last_first_center_difftime          --入末中心时间和出首中心时间差
     ,if(inout_last_center_difftime          >0,inout_last_center_difftime          ,0) as inout_last_center_difftime          --入末中心时间和出末中心时间差
     ,if(dest_last_center_difftime           >0,dest_last_center_difftime           ,0) as dest_last_center_difftime           --网点到件时间和出末中心时间差
     ,if(pre_sign_dest_difftime              >0,pre_sign_dest_difftime              ,0) as pre_sign_dest_difftime              --时效签收和网点到件时间差
     ,if(real_pre_sign_difftime              >0,real_pre_sign_difftime              ,0) as real_pre_sign_difftime              --实际签收时间和时效签收时间差
     ,if(network_taking_send_difftime        >0,network_taking_send_difftime        ,0) as network_taking_send_difftime        --网点揽收和网点发件时间差
     ,if(network_send_nodal_arrival_difftime >0,network_send_nodal_arrival_difftime ,0) as network_send_nodal_arrival_difftime --网点发件和集散到件时间差
     ,if(first_nodal_arrival_send_difftime   >0,first_nodal_arrival_send_difftime   ,0) as first_nodal_arrival_send_difftime   --始发集散点到件和发件时间差
     ,if(nodal_send_center_arrival_difftime  >0,nodal_send_center_arrival_difftime  ,0) as nodal_send_center_arrival_difftime  --始发集散点发件和始发中心到件时间差
     ,if(nodal_arrival_center_send_difftime  >0,nodal_arrival_center_send_difftime  ,0) as nodal_arrival_center_send_difftime  --末端集散点到件和末端中心发件时间差
     ,if(end_nodal_arrival_send_difftime     >0,end_nodal_arrival_send_difftime     ,0) as end_nodal_arrival_send_difftime     --末端集散点到件和发件时间差
     ,if(nodal_send_network_arrival_difftime >0,nodal_send_network_arrival_difftime ,0) as nodal_send_network_arrival_difftime --末端集散点发件和末端网点到件时间差
     ,if(end_network_arrival_deliver_difftime>0,end_network_arrival_deliver_difftime,0) as end_network_arrival_deliver_difftime--末端网点到件和网点派件时间差
     ,if(deliver_aging_sign_difftime         >0,deliver_aging_sign_difftime         ,0) as deliver_aging_sign_difftime         --时效签收和末端网点派件时间差
     ,if(actual_aging_sign_difftime          >0,actual_aging_sign_difftime          ,0) as actual_aging_sign_difftime          --实际签收和时效签收时间差
     ,is_reback           --是否退转件
     ,is_difficult        --是否问题件
     ,is_end_piece        --是否完结件
     ,is_intercept        --是否拦截件
     ,if_reback           --是否问题件_剔除退还撤销销场景
     ,if_end_piece        --是否完结件_限制客户取消寄件、作废件场景
     ,if_intercept        --是否拦截件_剔除拦截撤销场景 
     ,end_site_arrival_later_time --末端网点最晚到件时间
     ,deliver_scan_time           --派件时间(相同网点取最早，不同网点取最晚)
     ,instore_scan_time           --入库时间
     ,outstore_scan_time          --出库时间
     ,actual_taking_scan_time     --实际揽收时间
     ,actual_sign_scan_time       --实际签收时间
     ,aging_sign_scan_time        --时效签收时间
     ,customer_order_time         --客户下单时间
     ,if(custorder_taking_difftime         >0,custorder_taking_difftime    ,0) as custorder_taking_difftime    --时效签收和末端网点派件时间差
     ,if(custorder_pre_sign_difftime       >0,custorder_pre_sign_difftime  ,0) as custorder_pre_sign_difftime  --实际签收和时效签收时间差 
     ,send_virt_code      --始发虚拟代理区编码
     ,send_virt_name      --始发虚拟代理区名称 
     ,sign_virt_code      --签收虚拟代理区编码
     ,sign_virt_name      --签收虚拟代理区名称
     ,if_arrival_deliver  --是否到出一体
     ,if_deliver_township --是否出仓乡镇
     ,if_after_deliver    --是否稍后出仓
     ,after_deliver_time  --稍后出仓时间
     ,dt
from (
    select
          waybill_no
         ,to_date(actual_taking_scantime) as taking_date --实际揽收日期
         ,to_date(aging_sign_scantime)    as sign_date   --实效签收日期              
         ,send_network_code   --始发网点编码          
         ,send_network_name   --始发网点名称            
         ,send_area_id        --始发区县id          
         ,send_area_desc      --始发区县名称          
         ,send_city_id        --始发城市id          
         ,send_city_desc      --始发城市名称          
         ,send_agent_code     --始发代理区code       
         ,send_agent_name     --始发代理区名称         
         ,send_fran_code      --始发加盟商code       
         ,send_fran_name      --始发加盟商名称         
         ,send_provider_id    --始发省份id          
         ,send_provider_desc  --始发省份名称          
         ,send_regional_id    --始发大区id          
         ,send_regional_desc  --始发大区名称   
         ,send_virt_code      --始发虚拟代理区编码
         ,send_virt_name      --始发虚拟代理区名称   
         ,sign_network_code   --签收网点编码          
         ,sign_network_name   --签收网点名称           
         ,sign_area_id        --签收区县id          
         ,sign_area_desc      --签收区县名称          
         ,sign_city_id        --签收城市id          
         ,sign_city_desc      --签收城市名称          
         ,sign_agent_code     --签收代理区code       
         ,sign_agent_name     --签收代理区名称         
         ,sign_fran_code      --签收加盟商code       
         ,sign_fran_name      --签收加盟商名称         
         ,sign_provider_id    --签收省份id          
         ,sign_provider_desc  --签收省份名称          
         ,sign_regional_id    --签收大区id          
         ,sign_regional_desc  --签收大区名称  
         ,sign_virt_code      --签收虚拟代理区编码
         ,sign_virt_name      --签收虚拟代理区名称
         ,ordersource_code    --多平台订单来源编码
         ,ordersource_name    --多平台订单来源名称
         ,deliver_user_code   --派件员编码
         ,deliver_user_name   --派件员名称
         ,is_reback           --是否退转件
         ,is_difficult        --是否问题件
         ,is_end_piece        --是否完结件
         ,is_intercept        --是否拦截件
         ,if_reback           --是否问题件_剔除退还撤销销场景
         ,if_end_piece        --是否完结件_限制客户取消寄件、作废件场景
         ,if_intercept        --是否拦截件_剔除拦截撤销场景
         ,round((unix_timestamp(aging_sign_scantime             ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(actual_taking_scantime         ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as pre_sign_taking_difftime     --时效签收时间和揽收时间差
         ,round((unix_timestamp(actual_sign_scantime            ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(actual_taking_scantime         ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as real_sign_taking_difftime    --实际签收时间和揽收时间差
         ,round((unix_timestamp(first_center_arrival_early_time ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(actual_taking_scantime         ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as first_center_taking_difftime --揽收和入首中心时间差
         ,round((unix_timestamp(first_center_send_later_time    ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(first_center_arrival_early_time,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as inout_first_center_difftime  --出首中心时间和入首中心时间差
         ,round((unix_timestamp(end_site_arrival_later_time     ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(first_center_arrival_early_time,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as dest_first_center_difftime   --目的网点到件时间和入首中心时间差
         ,round((unix_timestamp(end_center_arrival_early_time   ,'yyyy-MM-ddHH:mm:ss') - unix_timestamp(first_center_send_later_time   ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as last_first_center_difftime   --入末中心时间和出首中心时间差
         ,case when if_reback <> 1 and if_end_piece <> 1 and if_intercept <> 1 and first_center_network_code = end_center_network_code then null
               else round((unix_timestamp(end_center_send_later_time      ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(end_center_arrival_early_time  ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) end as inout_last_center_difftime   --入末中心时间和出末中心时间差
         ,round((unix_timestamp(end_site_arrival_later_time     ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(end_center_send_later_time     ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as dest_last_center_difftime    --网点到件时间和出末中心时间差
         ,round((unix_timestamp(aging_sign_scantime             ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(end_site_arrival_later_time    ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as pre_sign_dest_difftime       --时效签收和网点到件时间差
         ,round((unix_timestamp(actual_sign_scantime            ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(aging_sign_scantime            ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as real_pre_sign_difftime       --实际签收时间和时效签收时间差
         ,round((unix_timestamp(first_network_send_later_time   ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(actual_taking_scantime         ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as network_taking_send_difftime         --网点揽收和网点发件时间差
         ,round((unix_timestamp(first_nodal_arrival_early_time  ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(first_network_send_later_time  ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as network_send_nodal_arrival_difftime  --网点发件和集散到件时间差
         ,round((unix_timestamp(first_nodal_send_later_time     ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(first_nodal_arrival_early_time ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as first_nodal_arrival_send_difftime    --始发集散点到件和发件时间差
         ,round((unix_timestamp(first_center_arrival_early_time ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(first_nodal_send_later_time    ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as nodal_send_center_arrival_difftime   --始发集散点发件和始发中心到件时间差
         ,round((unix_timestamp(end_nodal_arrival_early_time    ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(end_center_send_later_time     ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as nodal_arrival_center_send_difftime   --末端集散点到件和末端中心发件时间差
         ,round((unix_timestamp(end_nodal_send_later_time       ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(end_nodal_arrival_early_time   ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as end_nodal_arrival_send_difftime      --末端集散点到件和发件时间差
         ,round((unix_timestamp(end_site_arrival_later_time     ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(end_nodal_send_later_time      ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as nodal_send_network_arrival_difftime  --末端集散点发件和末端网点到件时间差
         ,round((unix_timestamp(deliver_scan_time               ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(end_site_arrival_later_time    ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as end_network_arrival_deliver_difftime --末端网点到件和网点派件时间差   --修改点 
         ,round((unix_timestamp(aging_sign_scantime             ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(deliver_scan_time              ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as deliver_aging_sign_difftime          --时效签收和末端网点派件时间差   --修改点
         ,round((unix_timestamp(end_site_arrival_later_time     ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(end_nodal_send_later_time      ,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as actual_aging_sign_difftime           --实际签收和时效签收时间差
         ,end_site_arrival_later_time as end_site_arrival_later_time --末端网点最晚到件时间                         --修改点
         ,deliver_scan_time           as deliver_scan_time           --派件时间(相同网点取最早，不同网点取最晚)     --修改点
         ,instore_scan_time           as instore_scan_time           --入库时间                                     --修改点
         ,outstore_scan_time          as outstore_scan_time          --出库时间                                     --修改点
         ,actual_taking_scantime      as actual_taking_scan_time     --实际揽收时间                                 --修改点
         ,actual_sign_scantime        as actual_sign_scan_time       --实际签收时间                                 --修改点
         ,aging_sign_scantime         as aging_sign_scan_time        --时效签收时间                                 --修改点   --修改点 
         ,customer_order_time         as customer_order_time         --客户下单时间
         ,round((unix_timestamp(actual_taking_scantime ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(case when customer_order_time <= actual_taking_scantime then customer_order_time else actual_taking_scantime end,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as custorder_taking_difftime    --客户下单-实际揽收时间差
         ,round((unix_timestamp(aging_sign_scantime    ,'yyyy-MM-dd HH:mm:ss') - unix_timestamp(case when customer_order_time <= actual_taking_scantime then customer_order_time else actual_taking_scantime end,'yyyy-MM-dd HH:mm:ss'))/3600.0,2) as custorder_pre_sign_difftime  --客户下单-时效签收时间差
         ,if_arrival_deliver --是否到出一体
         ,if_deliver_township --是否出仓乡镇
         ,if_after_deliver --是否稍后出仓
         ,after_deliver_time --稍后出仓时间
         ,to_date(actual_taking_scantime) as dt --分区
    from (
         select
               wide.waybill_no
              ,wide.ordersource_code                --多平台订单来源编码
              ,case when wide.ordersource_name not in ('桃花岛','桃花岛跨境','桃花岛跨境（逆）','逍遥峰','紫金山','七星潭','极地湾','麦田圈','有赞','苏宁','唯品会面单')
                      then '其他' else wide.ordersource_name end as ordersource_name  --多平台订单来源名称
              ,wide.first_network_network_code      --始发网点编码
              ,wide.first_network_send_later_time   --始发网点最晚装车发件时间
              ,wide.first_nodal_network_code        --始发集散网点
              ,wide.first_nodal_arrival_early_time  --始发集散最早到件时间
              ,wide.first_nodal_send_later_time     --始发集散最晚装车发件时间
              ,wide.first_center_network_code       --始发转运中心
              ,wide.first_center_arrival_early_time --始发中心最早到件时间
              ,wide.first_center_send_later_time    --始发中心最晚发件时间
              ,wide.end_center_network_code         --末端转运中心
              ,wide.end_center_arrival_early_time   --末端中心最早到件时间
              ,wide.end_center_send_later_time      --末端中心最晚装车发件实际发车时间
              ,wide.end_nodal_network_code          --目的集散网点
              ,wide.end_nodal_arrival_early_time    --目的集散最早到件时间
              ,wide.end_nodal_send_later_time       --目的集散最晚装车发件实际发车时间
              ,wide.end_site_arrival_later_time     --末端网点最最早到件时间
              ,wide.pick_network_code               --寄件网点编码
              ,if(wide.actual_taking_scantime<>'2099-12-31 00:00:00',wide.actual_taking_scantime,null)  as actual_taking_scantime     --实际揽收时间
              ,if(wide.actual_sign_scantime  <>'2099-12-31 00:00:00',wide.actual_sign_scantime  ,null)  as actual_sign_scantime       --实际签收时间
              ,if(wide.aging_sign_scantime   <>'2099-12-31 00:00:00',wide.aging_sign_scantime   ,null)  as aging_sign_scantime        --时效签收时间
              ,wide.actual_taking_scansitecode as actual_taking_scansitecode--实际揽收网点
              ,wide.actual_sign_scansitecode   as actual_sign_scansitecode --实际签收网点
              ,wide.aging_sign_scansitecode    as aging_sign_scansitecode  --实效签收网点
              ,dim_send.code                   as send_network_code        --始发网点编码
              ,dim_send.name                   as send_network_name        --始发网点名称
              ,dim_sign.code                   as sign_network_code        --签收网点编码
              ,dim_sign.name                   as sign_network_name        --签收网点名称
              ,dim_send.area_id                as send_area_id             --始发区县id
              ,dim_send.area_desc              as send_area_desc           --始发区县名称
              ,dim_send.city_id                as send_city_id             --始发城市id
              ,dim_send.city_desc              as send_city_desc           --始发城市名称
              ,dim_send.agent_code             as send_agent_code          --始发代理区code
              ,dim_send.agent_name             as send_agent_name          --始发代理区名称
              ,dim_send.fran_code              as send_fran_code           --始发加盟商code
              ,dim_send.fran_name              as send_fran_name           --始发加盟商名称
              ,dim_send.provider_id            as send_provider_id         --始发省份id
              ,dim_send.provider_desc          as send_provider_desc       --始发省份名称
              ,dim_send.regional_id            as send_regional_id         --始发大区id
              ,dim_send.regional_desc          as send_regional_desc       --始发大区名称
              ,dim_send.virt_code              as send_virt_code           --始发虚拟代理区编码
              ,dim_send.virt_name              as send_virt_name           --始发虚拟代理区名称
              ,dim_sign.area_id                as sign_area_id             --签收区县id
              ,dim_sign.area_desc              as sign_area_desc           --签收区县名称
              ,dim_sign.city_id                as sign_city_id             --签收城市id
              ,dim_sign.city_desc              as sign_city_desc           --签收城市名称
              ,dim_sign.agent_code             as sign_agent_code          --签收代理区code
              ,dim_sign.agent_name             as sign_agent_name          --签收代理区名称
              ,dim_sign.fran_code              as sign_fran_code           --签收加盟商code
              ,dim_sign.fran_name              as sign_fran_name           --签收加盟商名称
              ,dim_sign.provider_id            as sign_provider_id         --签收省份id
              ,dim_sign.provider_desc          as sign_provider_desc       --签收省份名称
              ,dim_sign.regional_id            as sign_regional_id         --签收大区id
              ,dim_sign.regional_desc          as sign_regional_desc       --签收大区名称
              ,dim_sign.virt_code              as sign_virt_code           --签收虚拟代理区编码
              ,dim_sign.virt_name              as sign_virt_name           --签收虚拟代理区名称
              ,nvl(wide.is_reback     ,0)      as is_reback                --是否退转件
              ,nvl(wide.is_difficult  ,0)      as is_difficult             --是否问题件
              ,nvl(wide.is_end_piece  ,0)      as is_end_piece             --是否完结件
              ,nvl(wide.is_intercept  ,0)      as is_intercept             --是否拦截件
              ,nvl(normal.if_reback   ,0)      as if_reback                --是否问题件_剔除退还撤销销场景
              ,nvl(normal.if_end_piece,0)      as if_end_piece             --是否完结件_限制客户取消寄件、作废件场景
              ,nvl(normal.if_intercept,0)      as if_intercept             --是否拦截件_剔除拦截撤销场景 
              ,wide.deliver_scan_time          as deliver_scan_time        --派件时间(相同网点取最早，不同网点取最晚)    --修改点 
              ,wide.instore_scan_time          as instore_scan_time        --入库时间(相同网点取最早，不同网点取最晚)    --修改点  
              ,wide.outstore_scan_time         as outstore_scan_time       --出库时间(相同网点取最早，不同网点取最晚)    --修改点 
              ,wide.sign_scan_time             as sign_scan_time           --签收时间(相同网点取最早，不同网点取最晚)    --修改点
              ,wide.deliver_user_code          as deliver_user_code        --出仓员编码(相同网点取最早，不同网点取最晚)  --修改点 
              ,wide.deliver_user_name          as deliver_user_name        --出仓员名称(相同网点取最早，不同网点取最晚)  --修改点
              ,greatest(nvl(wide.check_flag_early,0),nvl(wide.check_flag_later,0)) as if_arrival_deliver --是否到出一体
              ,wide.customer_order_time        as customer_order_time      --客户下单时间
              ,wide.if_deliver_township        as if_deliver_township      --是否出仓乡镇
              ,wide.if_after_deliver --是否稍后出仓
              ,wide.after_deliver_time --稍后出仓时间
         from (
             select  
                  *
                 ,least(nvl(taking_early_scantime,'2099-12-31 00:00:00'),nvl(warehousing_early_scantime,'2099-12-31 00:00:00')) as actual_taking_scantime --实际揽收时间
                 ,least(nvl(outstore_scan_time   ,'2099-12-31 00:00:00'),nvl(sign_scan_time            ,'2099-12-31 00:00:00')) as actual_sign_scantime   --实际签收时间
                 ,least(nvl(sign_scan_time       ,'2099-12-31 00:00:00'),nvl(outstore_scan_time        ,'2099-12-31 00:00:00'),nvl(instore_scan_time,'2099-12-31 00:00:00')) as aging_sign_scantime --实效签收时间
                 ,nvl(taking_early_scansitecode   ,warehousing_early_scansitecode) as actual_taking_scansitecode --实际揽收网点
                 ,nvl(outstore_early_scansitecode ,sign_early_scansitecode       ) as actual_sign_scansitecode   --实际签收网点
                 ,coalesce(sign_early_scansitecode,outstore_early_scansitecode,instore_early_scansitecode) as aging_sign_scansitecode --实效签收网点
                 ,if(end_site_arrival_later_time <= deliver_early_scan_time,deliver_early_scan_time,deliver_later_scantime ) as deliver_scan_time --派件时间
                 ,if(end_site_arrival_later_time <= deliver_early_scan_time,deliver_early_usercode,deliver_later_user_code ) as deliver_user_code --派件员编码
                 ,if(end_site_arrival_later_time <= deliver_early_scan_time,deliver_early_username,deliver_later_user      ) as deliver_user_name --派件员名称
             from (
                 select
                      wide.*
                     ,der.if_deliver_township
                     ,if(instore_early_scansitecode  = instore_later_scansitecode ,instore_early_scantime  ,instore_later_scantime  ) as instore_scan_time         --入库时间   (相同网点取最早，不同网点取最晚)
                     ,if(outstore_early_scansitecode = outstore_later_scansitecode,outstore_early_scantime ,outstore_later_scantime ) as outstore_scan_time        --出库时间   (相同网点取最早，不同网点取最晚)
                     ,if(sign_early_scansitecode     = sign_later_scansitecode    ,sign_early_scantime     ,sign_later_scantime     ) as sign_scan_time            --签收时间   (相同网点取最早，不同网点取最晚)
                     ,if(deliver_early_scansitecode  = deliver_later_scansitecode ,deliver_early_scantime  ,deliver_later_scantime  ) as deliver_early_scan_time   --派件时间   (相同网点取最早，不同网点取最晚)
                     ,if(deliver_early_scansitecode  = deliver_later_scansitecode ,der.deliver_early_user_code ,der.deliver_later_user_code) as deliver_early_usercode    --派件员编码 (相同网点取最早，不同网点取最晚)
                     ,if(deliver_early_scansitecode  = deliver_later_scansitecode ,der.deliver_early_user_name ,der.deliver_later_user_name) as deliver_early_username    --派件员名称 (相同网点取最早，不同网点取最晚)
                     ,delay_der.after_deliver_time --稍后出仓时间
                     ,case when delay_der.after_deliver_time is not null then 1 else 0 end as if_after_deliver
                 from jms_dwd.dwd_wide_unsign_summary_waybill_dt wide
                 left join (
                     select *
                     from(
                         select
                              waybill_no
                             ,first_value(send_user_code) over(partition by waybill_no order by scan_time) as deliver_early_user_code --最早扫描员编码
                             ,first_value(send_user     ) over(partition by waybill_no order by scan_time) as deliver_early_user_name --最早扫描员名称
                             ,first_value(send_user_code) over(partition by waybill_no order by scan_time desc) as deliver_later_user_code --最晚扫描员编码
                             ,first_value(send_user     ) over(partition by waybill_no order by scan_time desc) as deliver_later_user_name --最晚扫描员名称
                             ,max(case when width is not null then 1 else 0 end) over(partition by waybill_no) as if_deliver_township --是否出仓乡镇
                             ,row_number() over(partition by waybill_no order by scan_time) as rn
                         from jms_dwd.dwd_tab_barscan_deliver_base_dt --派件
                         where dt between date_sub('{{ execution_date | cst_ds }}',29) and '{{ execution_date | cst_ds }}'
                     ) a where a.rn = 1
                 ) der on wide.waybill_no = der.waybill_no
                 left join (
                     select *
                     from (
                         select
                             waybill_id as waybill_no
                            ,operate_time as after_deliver_time
                            ,row_number() over(partition by waybill_id order by scan_time desc) rn
                         from jms_dwd.dwd_tab_barscan_delay_deliver_base_dt
                         where dt between date_sub ('{{ execution_date | cst_ds }}',40) and '{{ execution_date | cst_ds }}'
                     ) a where a.rn = 1
                 ) delay_der on wide.waybill_no = delay_der.waybill_no
                 where wide.dt between date_sub('{{ execution_date | cst_ds }}',29) and '{{ execution_date | cst_ds }}'  --取30天未签收宽表
             ) a 
         ) wide
         left join (
             select
                   waybill_no    --运单号
                  ,case when (is_reback  = 1 and to_date(reback_print_time) between date_sub('{{ execution_date | cst_ds }}',60) and '{{ execution_date | cst_ds }}') then is_reback else 0 end as if_reback  --剔除退转撤销
                  ,case when (is_end_piece = 1 and end_piece_type_code in (5,44) and to_date(end_piece_input_time) between date_sub('{{ execution_date | cst_ds }}',60) and '{{ execution_date | cst_ds }}') then is_end_piece else 0 end as if_end_piece  --是否完结件
                  ,case when (is_intercept = 1 and to_date(intercept_time) between date_sub('{{ execution_date | cst_ds }}',60) and '{{ execution_date | cst_ds }}') then is_intercept else 0 end as if_intercept  --是否拦截件
             from jms_dwd.dwd_wide_abnormal_detail_waybill_dt  --异常件宽表
             where dt between date_sub('{{ execution_date | cst_ds }}',60) and '{{ execution_date | cst_ds }}'
         ) normal on wide.waybill_no = normal.waybill_no
         left join jms_dim.dim_network_whole_massage dim_send --揽收维度
             on dim_send.code=wide.actual_taking_scansitecode
            and wide.actual_taking_scansitecode is not null
         left join jms_dim.dim_network_whole_massage dim_sign --签收维度
             on dim_sign.code = wide.aging_sign_scansitecode
            and wide.aging_sign_scansitecode is not null
         where to_date(wide.actual_taking_scantime) between date_sub('{{ execution_date | cst_ds }}',30) and '{{ execution_date | cst_ds }}' --取30天最早揽收/入仓时间
    ) detail
) a
distribute by dt,pmod(hash(rand()),150);